# encoding=utf-8
import aiohttp
import asyncio
import json
import pprint
import xlrd # 读
from openpyxl import Workbook #读写
import time


header = {}
header["token"] = '''eyJhbGciOiJIUzI1NiJ9.eyJqdGkiOiJNRU1CRVJfQ0VOVEVSX0pXVF9JRCIsImlhdCI6MTY1MDAxMTA1NywiaXNzIjoibWVtYmVyQ2VudGVyIiwic3ViIjoie1wiaWRcIjo0MzgsXCJsb2dpbk5hbWVcIjpcImxsaTE4NWZvcmRcIixcInJlZnJlc2hUb2tlblwiOlwiZXlKaGJHY2lPaUpJVXpJMU5pSXNJblI1Y0NJZ09pQWlTbGRVSWl3aWEybGtJaUE2SUNJNU5HRXlaR1ExWkMwNU1HVm1MVFExTW1VdE9HWmhaaTB3WlRWa05EWXlZVEpoWTJVaWZRLmV5SmxlSEFpT2pFMk5UQXdNVFE1TlRjc0ltbGhkQ0k2TVRZMU1EQXhNVEExTnl3aWFuUnBJam9pWTJFNFpEUTJNalV0WVRObVl5MDBaRFV4TFdFNU9HRXROVFEwWm1VMVlUZ3lPV0kwSWl3aWFYTnpJam9pYUhSMGNITTZMeTloZFhSb0xtVjJjMk55YlM1bWIzSmtMbU52YlM1amJpOWhkWFJvTDNKbFlXeHRjeTl0WVhOMFpYSWlMQ0poZFdRaU9pSm9kSFJ3Y3pvdkwyRjFkR2d1WlhaelkzSnRMbVp2Y21RdVkyOXRMbU51TDJGMWRHZ3ZjbVZoYkcxekwyMWhjM1JsY2lJc0luTjFZaUk2SW1aak5qYzNZekF4TFRFM05XVXRORFpqWVMwNFpqQTNMVGcwTUdabE1UZG1aakEzT0NJc0luUjVjQ0k2SWxKbFpuSmxjMmdpTENKaGVuQWlPaUp0WlcxaVpYSXRZV1J0YVc0aUxDSnpaWE56YVc5dVgzTjBZWFJsSWpvaU1qRXlOVGxrTURZdE5EbGhaUzAwTjJWbUxXRmxPR1F0Wm1RMk9UVXdPVGczTlRGbUlpd2ljMk52Y0dVaU9pSndjbTltYVd4bEluMC5fZVhNbnhSOFFxVFlsTE9OS0VkNDJZVzF1OVE3MmplNUtZZUppYUNFYk80XCIsXCJyb2xlQ29kZVwiOlwiTWVtYmVyX2luZm9cIixcInVzZXJOYW1lXCI6XCJMaW5hbiBMaVwifSIsImV4cCI6MTY1MDA5NzQ1N30.dcJThz61BwQvTEqomLQ1TzsAiK_MWdJoFU6-c7tn_0Y'''
read_excel_path = "./0406SEP相关原始数据梳理统计.xls"
read_excel_sheet = "Sheet1"
write_excel_path = "./query_result_20220415.xlsx"


# 用户信息数据结构
class user_item:
    def __init__(self):
        self.phone_no = 0
        self.create_time = ""
        self.integral = 0
    def debug(self):
        return "phone_no: {0}, create_time = {1}, integral = {2}".format(self.phone_no, self.create_time, self.integral)
    def __str__(self) -> str:
        return self.debug()
    def __repr__(self) -> str:
        return self.debug()
    # 返回title
    def title():
        l = []
        phone = "手机号"
        ct    = "入会时间"
        score = "积分"
        l.append(phone)
        l.append(ct)
        l.append(score)
        return l
    
    # 转换成list
    def convert_to_list(self):
        result = []
        result.append(str(self.phone_no))
        result.append(str(self.create_time))
        result.append(str(self.integral))
        return result


# 从excel读手机号
def read_phone_list_from_excel():
    excel_path = read_excel_path
    eb = xlrd.open_workbook(excel_path)
    s1 = eb.sheet_by_name(read_excel_sheet)
    num_rows = s1.nrows
    phone_list = []
    for index in range(1, num_rows):
        pn = int(s1.cell_value(index, 10))
        phone_list.append(int(pn))
    return phone_list

# 
def write_data_to_excel(all_line):
    wb = Workbook()
    ws = wb.active
    ws.title = "Sheet0"
    for li in all_line:
        ws.append(li)
    wb.save(write_excel_path)

# 信息写入excel
def write_user_info_into_excel(user_info_map):
    excel_path = read_excel_path
    eb = xlrd.open_workbook(excel_path)
    s1 = eb.sheet_by_name(read_excel_sheet)
    num_rows = s1.nrows
    if num_rows < 1:
        print("table is illegel")
        return

    all_line = []   # 用户存放表的所有行
    # 先写表头
    lv = s1.row_values(0)
    lv.extend(user_item.title())
    all_line.append(lv)
    # 写表值
    for index in range(1, num_rows):
        lv = s1.row_values(index) # 获取该行所属数据
        phone_no = int(lv[10])
        ui = user_item()
        if phone_no in user_info_map.keys():
            ui = user_info_map[phone_no]
        lv.extend(ui.convert_to_list())
        all_line.append(lv)
    write_data_to_excel(all_line)


# 根据电话查询信息
async def query_info_by_phone_no(session : aiohttp.ClientSession, phone_no):
    url="https://portal.evscrm.ford.com.cn/member-admin/usercenter/userinfo-list?pageNo=1&pageSize=10&startTime=&endTime=&aId=&nickName=&phoneNum={PhoneNo}&joinChannel=&cutFlag=0&memberFlag=".format(PhoneNo=phone_no)
    hd = header
    async with session.get(url, headers=hd) as resp:
        status = resp.status
        if 200 == status:
            jd = await resp.json()
            # print(jd)
            result = {}
            if "errorMessage" in jd.keys():
                if jd["errorMessage"] == "证失败":
                    return result
            result_list = jd["result"]
            llen = len(result_list)
            if 0 != llen:
                result = result_list[0]
        return status, result


async def merge_handle(semaphore, session : aiohttp.ClientSession, phone_no):
    status = 0
    result = {}
    async with semaphore:
        while 200 != status:
            status, result = await query_info_by_phone_no(session, phone_no)
        return result


# 处理请求结果
def handle_request_result(result_list):
    user_info_map = {} # 用户信息映射 {"13627291809": user_item()}
    for result in result_list:
        if not isinstance(result, dict):
            # print("手机号: " + str(phone_no) + " 结果错误")
            continue
        if {} == result:
            # print("手机号: " + str(phone_no) + " 没有匹配的信息")
            continue
        ui = user_item()
        phone_no = 0
        # 手机号
        if "mobile" in result.keys():
            phone_no = int(result["mobile"])
        else:
            continue
        ui.phone_no = phone_no
        if "createTime" in result.keys():
            create_time = result["createTime"]
            integral    = result["integral"]
            ui.create_time = create_time
            ui.integral    = integral
            if phone_no in result.keys():
                print("出错 跳过")
                continue
            else:
                user_info_map[phone_no] = ui
            print("手机号: " + str(phone_no) + " 入会时间: " + str(create_time) + " 积分: " + str(integral))
        else:
            print("手机号: " + str(phone_no) + " 查询信息失败")
    return user_info_map


async def handler():
    begin_time = time.time()
    # 1.从excel里面读手机号
    phone_list = read_phone_list_from_excel()
    result_list = []
    async with aiohttp.ClientSession(connector=aiohttp.TCPConnector(ssl=False)) as session:
        semaphore = asyncio.Semaphore(100)
        l = []
        for p in phone_list:
            l.append(merge_handle(semaphore, session, p))
        result_list = await asyncio.gather(*l)
        user_info_map = {} # 用户信息映射 {"13627291809": user_item()}
        user_info_map = handle_request_result(result_list)
        # 3.写入excel
        write_user_info_into_excel(user_info_map)
    end_time = time.time()
    dt = int(end_time - begin_time)
    print(" cost time: " + str(dt))

if __name__=="__main__":
    asyncio.run(handler())
